/*
 * @(#) WriteToExcel.java 2012-3-13 上午10:04:24
 *
 * Copyright 2012 Rockwell Automation, Inc. All rights reserved.
 * Rockwell Automation PROPRIETARY/CONFIDENTIAL. Use is subject to license terms.
 */package com.osi.mes.excel;

import java.util.Vector;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.hssf.util.Region;

//import com.osi.mes.qm.test.ATRow;

import com.datasweep.compatibility.client.ATRow;


public class WriteToExcel implements IBasicTitle
{
	public HSSFWorkbook wb;

	public HSSFFont bigTitleFont;

	public HSSFFont secTitleFont;

	public HSSFFont columnTitleFont;

	public HSSFFont contentFont;

	public HSSFFont bodyContentFont;
	
	public HSSFFont bottomContentFont;
	
	public static int DEFAULT_REPORT_BODY_ROW_NUM = 5;
	
	public static int COLUMN_WIDTH = 4000;
	

	public WriteToExcel()
	{
		if (wb != null)
			return;

		wb = new HSSFWorkbook();

		bigTitleFont = wb.createFont();
		bigTitleFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		bigTitleFont.setFontName("楷体_GB2312");
		bigTitleFont.setFontHeightInPoints((short) 20);

		secTitleFont = wb.createFont();
		secTitleFont.setFontName("新宋体");
		secTitleFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		secTitleFont.setFontHeightInPoints((short) 12);

		columnTitleFont = wb.createFont();
		columnTitleFont.setFontName("黑体");
		columnTitleFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		columnTitleFont.setFontHeightInPoints((short) 12);

		contentFont = wb.createFont();
		contentFont.setFontName("宋体");
		contentFont.setFontHeightInPoints((short) 8);

		bodyContentFont = wb.createFont();
		bodyContentFont.setFontName("宋体");
		bodyContentFont.setFontHeightInPoints((short) 9);
		
		bottomContentFont = wb.createFont();
		bottomContentFont.setFontName("黑体");
		bottomContentFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		bottomContentFont.setFontHeightInPoints((short) 14);
	}

	public HSSFSheet createSheet(String sheetName)
	{
		if (wb == null)
			wb = new HSSFWorkbook();

		return wb.createSheet(sheetName);
	}
	
	public HSSFWorkbook getWorkbook()
	{
		return wb;
	}

	public HSSFFont getBigTitleFont()
	{
		return bigTitleFont;
	}

	public void setBigTitleFont(HSSFFont bigTitleFont)
	{
		this.bigTitleFont = bigTitleFont;
	}

	public HSSFFont getSecTitleFont()
	{
		return secTitleFont;
	}

	public void setSecTitleFont(HSSFFont secTitleFont)
	{
		this.secTitleFont = secTitleFont;
	}

	public HSSFFont getColumnTitleFont()
	{
		return columnTitleFont;
	}

	public void setColumnTitleFont(HSSFFont columnTitleFont)
	{
		this.columnTitleFont = columnTitleFont;
	}

	public HSSFFont getContentFont()
	{
		return contentFont;
	}

	public void setContentFont(HSSFFont contentFont)
	{
		this.contentFont = contentFont;
	}

	public HSSFFont getBoldContentFont()
	{
		return bodyContentFont;
	}

	public void setBoldContentFont(HSSFFont boldContentFont)
	{
		this.bodyContentFont = boldContentFont;
	}

	public void createCommonTitle(HSSFSheet sheet,String title,String condition, String[] columns)
	{
			HSSFCellStyle bigTitleStyle = wb.createCellStyle();
			bigTitleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
			bigTitleStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
			bigTitleStyle.setFont(bigTitleFont);
			createCell(sheet,0,0,title,bigTitleStyle);
			mergedRegion(sheet,0,0,0,columns.length-1);
			
			HSSFCellStyle secTitleStyle = wb.createCellStyle();
			secTitleStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT);
			secTitleStyle.setVerticalAlignment(HSSFCellStyle.ALIGN_LEFT);
			secTitleStyle.setFont(secTitleFont);
			addBorderToStyle(secTitleStyle);
			createCell(sheet,1,0,condition,secTitleStyle);
			mergedRegion(sheet,1,0,1,columns.length-1);
			
			HSSFCellStyle columnTitleStyle = wb.createCellStyle();
			columnTitleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
			columnTitleStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
			columnTitleStyle.setFont(columnTitleFont);
			addBorderToStyle(columnTitleStyle);
			for(int i=0;i<columns.length;i++)
			{
				createCell(sheet,2,i, columns[i],columnTitleStyle);
			}
	}
	
	private void addBorderToStyle(HSSFCellStyle cellStyle)
	{
		if(cellStyle == null)
			return ;
		
		cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
		cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
		cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		cellStyle.setBottomBorderColor(HSSFColor.BLACK.index);
		cellStyle.setTopBorderColor(HSSFColor.BLACK.index);
		cellStyle.setRightBorderColor(HSSFColor.BLACK.index);
		cellStyle.setLeftBorderColor(HSSFColor.BLACK.index);
	}
	
	private void createCell(HSSFSheet sheet,int row,int column,String cellVal,HSSFCellStyle cellNumStyle)
	{
		HSSFRow rowObj = sheet.getRow((short)row);
		if(rowObj == null)
		{
			rowObj = sheet.createRow((short) row);
		}
		
		HSSFCell hcell = rowObj.getCell((short)column);
		if(hcell == null)
		{
			hcell = rowObj.createCell((short) column);
		}
		
		hcell.setCellType(HSSFCell.CELL_TYPE_STRING);
		HSSFRichTextString str = new HSSFRichTextString(cellVal);
		hcell.setCellStyle(cellNumStyle);
		hcell.setCellValue(str);
		sheet.setColumnWidth(
			(short)column, COLUMN_WIDTH);
	}
	
	public void mergedRegion(HSSFSheet sheet,int rowFrom, int colFrom, int rowTo, int colTo)
	{
		HSSFRow firstRow = sheet.getRow(rowFrom);
		if(firstRow == null)
			return ;
		
		HSSFCell firstCell = firstRow.getCell((short)colFrom);
		if(firstCell == null)
			return ;
		HSSFCellStyle cellStyle = firstCell.getCellStyle();
		
		for(int i=rowFrom;i<=rowTo;i++)
		{
			HSSFRow rr = sheet.getRow(i);
			if(rr == null)
				rr = sheet.createRow(i);
			
			for(int j=colFrom;j<=colTo;j++)
			{
				HSSFCell cell = rr.getCell((short)j);
				if(cell == null)
				{
					cell = rr.createCell((short)j);
					cell.setCellType(HSSFCell.CELL_TYPE_STRING);
					cell.setCellStyle(cellStyle);
				}
			}
		}
		
		Region region = new Region(rowFrom, (short)colFrom, rowTo, (short)colTo);
		sheet.addMergedRegion(region);// 指定合并区域
	}
	
	
	public void createReportBody(HSSFSheet sheet,Vector<ATRow> atRows,String[] keys)
	{
		if(atRows != null)
		{
			
				HSSFCellStyle bodyContentCellStyle = wb.createCellStyle();
				bodyContentCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
				bodyContentCellStyle.setFont(bodyContentFont);
				bodyContentCellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
				addBorderToStyle(bodyContentCellStyle);
				for(int i=0;i<atRows.size();i++)
				{
					createReportBodyRow(sheet,i+3,bodyContentCellStyle,atRows.get(i),keys);
				}
		}
	}
	
	private void createReportBodyRow(HSSFSheet sheet,int row,HSSFCellStyle cellNumStyle,ATRow atRow,String[] keys)
	{
		for(int i=0;i<keys.length;i++)
		{
			 Object value = atRow.getValue(keys[i]);
			 Object colVal = atRow.getValue(keys[i]);
			 String val = "";
			 if(colVal!=null)
			 {
				 val = colVal.toString();
			 }
			
			 createCell(sheet, row, i, val, cellNumStyle);
		}
	}
}
